﻿using Model;
using System;
using System.Collections.Generic;
using System.Data;
using System.Web;
using Utility;

namespace BLL
{
    /// <summary>
    /// 基础逻辑对象
    /// </summary>
    public class BaseBLL
    {
        public ServiceHelper ThkService = new ServiceHelper();

        /// <summary>
        /// 获取新的序列
        /// </summary>
        /// <param name="SequenceName">序列名</param>
        /// <returns>序列号</returns>
        public decimal GetSequence(string SequenceName)
        {
            try
            {
                var dt = Query(string.Format("SELECT {0}.NEXTVAL FROM dual", SequenceName), "获取序列", "", "", "");
                if (dt != null && dt.Rows.Count > 0)
                {
                    return Convert.ToDecimal(dt.Rows[0][0]);
                }
                throw new Exception("获取序列异常");
            }
            catch (Exception)
            {
                throw new Exception("获取序列异常");
            }
        }

        /// <summary>
        /// 获取Count行数
        /// </summary>
        /// <param name="sql"></param>
        /// <returns>行数</returns>
        public int GetCount(string sql)
        {
            int count = -1;
            var dt = Query(sql);
            try
            {
                if (dt.Rows.Count != 0)
                {
                    count = Convert.ToInt32(dt.Rows[0][0]);
                }
            }
            catch (Exception)
            {
                throw new Exception("SQL COUNT 语句异常");
            }
            return count;
        }

        public int GetCount(string sql, List<Tuple<string, string, string>> parms)
        {
            int count = -1;
            var dt = Query(sql, parms);
            try
            {
                if (dt.Rows.Count != 0)
                {
                    count = Convert.ToInt32(dt.Rows[0][0]);
                }
            }
            catch (Exception)
            {
                throw new Exception("SQL COUNT 语句异常");
            }
            return count;
        }

        /// <summary>
        /// Sql查询（压缩数据传输，适用于数据量大情况）
        /// </summary>
        /// <param name="sql">Sql</param>
        /// <param name="win">模块名称</param>
        /// <param name="method">方法名</param>
        /// <param name="operatename">用户名</param>
        /// <param name="logonPerson">登录用户名</param>
        /// <returns></returns>
        public virtual DataTable Query(string sql, string win = "", string method = "", string operatename = "", string logonPerson = "")
        {
            return Query(sql, null);
        }

        /// <summary>
        /// Sql查询（DataTable直接传输）
        /// </summary>
        /// <param name="sql">Sql</param>
        /// <param name="win">模块名称</param>
        /// <param name="method">方法名</param>
        /// <param name="operatename">用户名</param>
        /// <param name="logonPerson">登录用户名</param>
        /// <returns></returns>
        public virtual DataTable GetData(string sql, string win = "", string method = "", string operatename = "", string logonPerson = "")
        {
            return Query(sql, null);
        }

        /// <summary>
        /// 增删改 非查询返回影响行数
        /// </summary>
        /// <param name="sql">Sql</param>
        /// <param name="operatename">用户名</param>
        /// <param name="win">模块名称</param>
        /// <param name="method">方法名</param>
        /// <returns>影响行数</returns>
        public virtual int NoQuery(string sql, string operatename = null, string win = "", string method = "")
        {
            return NoQuery(sql, null);
        }

        /// <summary>
        /// 返回第一条第一列数据
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public virtual object SingleOfNull(string sql)
        {
            var dt = Query(sql);
            if (dt.Rows.Count == 0)
            {
                return null;
            }
            return dt.Rows[0][0];
        }

        /// <summary>
        /// 返回第一条第一列数据
        /// </summary>
        /// <typeparam name="T">返回的类型</typeparam>
        /// <param name="sql">SQL语句</param>
        /// <param name="value">值</param>
        /// <returns>是否获取到数据</returns>
        public virtual bool SingleOfNull<T>(string sql, ref T value)
        {
            var _value = SingleOfNull(sql);
            if (_value == null)
            {
                return false;
            }

            Type t = typeof(T);
            value = (T)Convert.ChangeType(_value, t);
            return true;
        }

        /// <summary>
        /// 特殊字符转义
        /// </summary>
        /// <param name="value">需要转义的值</param>
        /// <returns>转义后的值</returns>
        public string Transference(string value)
        {
            if (value == null)
            {
                return value;
            }
            if (value == "")
            {
                return " ";
            }
            value = value.Replace("'", "''");
            return value;
            value = value.Replace("&", "||'&'||");
            value = value.Replace("-", "'/_%'escape'/'");
            return value;
        }

        /// <summary>
        /// 获取第一行
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public DataRow RowSingeleOfNull(string sql)
        {
            var dt = Query(sql);
            if (dt.Rows.Count == 0)
            {
                return null;
            }
            return dt.Rows[0];
        }

        private OracleHelp oraHelp = new OracleHelp();

        /// <summary>
        /// 提交事物
        /// </summary>
        /// <param name="ifGiveUp">是否放弃</param>
        /// <returns></returns>
        public bool Commit(bool ifGiveUp = false)
        {
            if (Tran == null)
            {
                throw new Exception("事物没有开启。");
            }
            else
            {
                if (ifGiveUp)
                {
                    Tran.Rollback();
                }
                else
                {
                    Tran.Commit();
                }
                Tran = null;
            }
            return true;
        }

        /// <summary>
        /// 开始事物
        /// </summary>
        public void BeginTran()
        {
            if (Tran == null)
            {
                Tran = Conn.BeginTransaction();
            }
            else
            {
                throw new Exception("事物已经打开,不能重复打开。");
            }
        }

        /// <summary>
        /// 分页查询
        /// </summary>
        /// <param name="sql">SQL语句</param>
        /// <param name="skip">跳过行</param>
        /// <param name="take">选择行</param>
        /// <returns>@RN 行码</returns>
        public virtual DataTable PageQuery(string sql, int? skip = null, int? take = null)
        {
            if (skip == null)
            {
                return Query(sql);
            }
            var _sql = @"SELECT
	* FROM (
		SELECT
			*
		FROM
			(
				SELECT
					T1.*, ROWNUM ORACLE_ROW_ID
				FROM
					({0}) T1
			) T1
		WHERE
			T1.ORACLE_ROW_ID <= {2}
	) T1
WHERE
	T1.ORACLE_ROW_ID > {1}";

            /*
            var _sql = @"SELECT
                            *
                         FROM
                            (SELECT
                                T1.*,
                                ROWNUM ORACLE_ROW_ID
                                FROM ({0}) T1) T1
                         WHERE T1.ORACLE_ROW_ID > {1} AND T1.ORACLE_ROW_ID <= {2}";
             */
            _sql = string.Format(_sql, sql, skip, skip + take);
            var dt = Query(_sql);
            dt.Columns.Remove("ORACLE_ROW_ID");
            return dt;
        }

        public virtual DataTable PageQuery2(string sql, int? skip = null, int? take = null)
        {
            if (skip == null)
            {
                return Query(sql);
            }
            var _sql = @"SELECT
	* FROM (
		SELECT
			*
		FROM
			(
				SELECT
					T1.*, ROWNUM ORACLE_ROW_ID
				FROM
					({0}) T1
			) T1
		WHERE
			T1.ORACLE_ROW_ID <= {2}
	) T1
WHERE
	T1.ORACLE_ROW_ID > {1}";

            /*
            var _sql = @"SELECT
                            *
                         FROM
                            (SELECT
                                T1.*,
                                ROWNUM ORACLE_ROW_ID
                                FROM ({0}) T1) T1
                         WHERE T1.ORACLE_ROW_ID > {1} AND T1.ORACLE_ROW_ID <= {2}";
             */
            _sql = string.Format(_sql, sql, skip, skip + take);
            var dt = Query(_sql);
            return dt;
        }

        /// <summary>
        /// 获取第一行数据
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public virtual DataRow FirstOrDefaultQuery(string sql)
        {
            var dt = PageQuery(sql, 0, 1);
            if (dt.Rows.Count > 0)
            {
                return dt.Rows[0];
            }
            return null;
        }

        /// <summary>
        /// 获取查询的行数(请注意与 GetCount 方法的区别)
        /// </summary>
        /// <param name="sql">Sql</param>
        /// <returns></returns>
        public virtual int SqlCount(string sql)
        {
            var _sql = @"SELECT
                            COUNT(*)
                         FROM ({0}) T";
            _sql = string.Format(_sql, sql);
            return GetCount(_sql);
        }

        #region 新方法

        public Service Service = new Service();

        public IDbTransaction Tran
        {
            get
            {
                return Service.DbTransaction;
            }
            set
            {
                Service.DbTransaction = value;
            }
        }

        public IDbConnection Conn
        {
            get
            {
                return Service.DbConnection;
            }
            set
            {
                Service.DbConnection = value;
            }
        }

        public virtual int SQLSExcute(List<Tuple<string, List<Tuple<string, string, string>>>> sqlArry)
        {
            try
            {
                OracleHelp ohelper = new OracleHelp();
                var result = 0;
                if (Conn == null)
                {
                    using (var conn = ohelper.GetConecttion())
                    {
                        foreach (var item in sqlArry)
                        {
                            result += ohelper.NoQuery(conn, item.Item1, item.Item2);
                        }
                    }
                    return result;
                }
                else
                {
                    foreach (var item in sqlArry)
                    {
                        result += ohelper.NoQuery(Conn, item.Item1, item.Item2);
                    }
                    return result;
                }
            }
            catch (Exception ex)
            {
                throw;
            }
        }

        public virtual int SQLSExcute(string sql)
        {
            List<Tuple<string, List<Tuple<string, string, string>>>> sqlArry = new List<Tuple<string, List<Tuple<string, string, string>>>>();
            foreach (var item in sql.Split(';'))
            {
                sqlArry.Add(new Tuple<string, List<Tuple<string, string, string>>>(item, new List<Tuple<string, string, string>>()));
            }
            return SQLSExcute(sqlArry);
        }

        public virtual int SQLSExcute(List<string> sql)
        {
            List<Tuple<string, List<Tuple<string, string, string>>>> sqlArry = new List<Tuple<string, List<Tuple<string, string, string>>>>();
            foreach (var item in sql)
            {
                sqlArry.Add(new Tuple<string, List<Tuple<string, string, string>>>(item, new List<Tuple<string, string, string>>()));
            }
            return SQLSExcute(sqlArry);
        }

        public virtual DataTable Query(string sql, List<Tuple<string, string, string>> parms)
        {
            try
            {
                OracleHelp ohelper = new OracleHelp();
                if (Conn == null)
                {
                    using (var conn = ohelper.GetConecttion())
                    {
                        return ohelper.Query(conn, sql, parms);
                    }
                }
                else
                {
                    return ohelper.Query(Conn, sql, parms);
                }
            }
            catch (Exception ex)
            {
                throw;
            }
        }

        public virtual int NoQuery(string sql, List<Tuple<string, string, string>> parms)
        {
            try
            {
                OracleHelp ohelper = new OracleHelp();
                if (Conn == null)
                {
                    using (var conn = ohelper.GetConecttion())
                    {
                        return ohelper.NoQuery(conn, sql, parms);
                    }
                }
                else
                {
                    return ohelper.NoQuery(Conn, sql, parms);
                }
            }
            catch (Exception ex)
            {
                throw;
            }
        }

        public IDbConnection GetDbConn()
        {
            if (Conn == null)
            {
                Conn = new OracleHelp().GetConecttion();
            }
            else
            {
                if (Conn.State == ConnectionState.Closed)
                {
                    Conn = new OracleHelp().GetConecttion();
                }
            }
            return Conn;
        }

        /// <summary>
        /// 当前请求用户 直接从
        /// </summary>
        public Sys_User User
        {
            get
            {
                if (user == null)
                {
                    user = (Sys_User)HttpContext.Current.Session["SysUserInfo"];
                }
                return user;
            }
            set
            {
                user = value;
            }
        }

        private Sys_User user = null;

        #endregion 新方法
    }

    /// <summary>
    /// Service 对象
    /// </summary>
    public class Service
    {
        public IDbConnection DbConnection { get; set; }
        public IDbTransaction DbTransaction { get; set; }
        public Sys_User User { get; set; }
    }
}